const db = require('../db');  // 引入数据库连接
const dayjs = require('dayjs');
const timezone = require('dayjs/plugin/timezone');
const utc = require('dayjs/plugin/utc');

// 使用插件
dayjs.extend(utc);
dayjs.extend(timezone);
// 创建订单
const createOrder = async (userId, totalPrice, shippingAddress) => {
    const [result] = await db.promise().query(
        `INSERT INTO orders (user_id, total_price, shipping_address) VALUES (?, ?, ?)`,
        [userId, totalPrice, shippingAddress]
    );
    return { order_id: result.insertId };
};

// 创建订单商品
const createOrderItems = async (orderId, items) => {
    console.log("创建订单商品");

    const queries = items.map(item => {
        return db.promise().query(
            `INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)`,
            [orderId, item.productId, item.quantity, item.price]
        );
    });
    await Promise.all(queries);
};

// 创建订单配送信息
const createOrderShipping = async (orderId, shippingInfo) => {
    await db.promise().query(
        `INSERT INTO order_shipping (order_id, recipient_name, recipient_phone, address) VALUES (?, ?, ?, ?)`,
        [orderId, shippingInfo.recipientName, shippingInfo.recipientPhone, shippingInfo.address]
    );
};

//更新商品支付状态

const updateOrderStatus = async (orderId, orderStatus) => {
    const query = 'UPDATE orders SET order_status = ? WHERE order_id = ?';
    try {
        // 检查 orderStatus 是否有效
        const validStatuses = ['pending', 'confirmed', 'shipped', 'delivered', 'cancelled'];
        if (!validStatuses.includes(orderStatus)) {
            throw new Error(`无效的订单状态：${orderStatus}`);
        }

        // 先检查订单是否存在
        const [rows] = await db.promise().query('SELECT * FROM orders WHERE order_id = ?', [orderId]);

        if (rows.length === 0) {
            throw new Error(`订单 ID ${orderId} 不存在`);
        }

        // 执行更新语句
        const [result] = await db.promise().query(query, [orderStatus, orderId]);

        // 如果没有影响到任何行，说明没有更新
        if (result.affectedRows === 0) {
            throw new Error(`更新订单状态失败，订单未被更新`);
        }

        // 返回查询结果对象
        return result;  // 包含 affectedRows 等信息
    } catch (error) {
        console.error('更新订单状态时发生错误:', error);
        throw error;  // 将错误抛出
    }
};




// 获取用户所有订单的详细信息
const getOrderDetails = async (userId) => {
    const [rows] = await db.promise().query(
        `SELECT 
            o.order_id, o.order_status, o.total_price, o.shipping_address, o.created_at,
            oi.order_item_id, oi.product_id, oi.quantity, oi.price,
            os.recipient_name, os.recipient_phone, os.address AS shipping_address,
             p.name AS product_name, 
            p.description AS product_description 
        FROM orders o
        LEFT JOIN order_items oi ON o.order_id = oi.order_id
        LEFT JOIN order_shipping os ON o.order_id = os.order_id
        LEFT JOIN products p ON oi.product_id = p.product_id
        WHERE o.user_id = ?
        ORDER BY o.created_at DESC`,
        [userId]
    );
    // 格式化时间，假设时区为 "Asia/Shanghai"
    const formattedRows = rows.map(order => {
        order.created_at = dayjs(order.created_at).tz('Asia/Shanghai').format('YYYY-MM-DD HH:mm:ss'); // 格式化时间并转为指定时区
        return order;
    });

    return formattedRows; // 返回格式化后的订单数据
};

// 获取单个订单详情
const getOrderById = async (orderId) => {
    const [rows] = await db.promise().query(
        `SELECT 
            o.order_id, o.order_status, o.total_price, o.shipping_address, o.created_at,
            oi.order_item_id, oi.product_id, oi.quantity, oi.price,
            os.recipient_name, os.recipient_phone, os.address AS shipping_address
            
        FROM orders o
        LEFT JOIN order_items oi ON o.order_id = oi.order_id
        LEFT JOIN order_shipping os ON o.order_id = os.order_id
        
        WHERE o.order_id = ?`,
        [orderId]
    );
    return rows[0];  // 返回单个订单
};


// 获取某个商品所有购买的订单
const getOrdersByProduct = async (productId) => {
    const [rows] = await db.promise().query(
        `SELECT 
            o.order_id, o.order_status, o.total_price, o.shipping_address, o.created_at,
            oi.quantity, oi.price, oi.product_id, 
            os.recipient_name, os.recipient_phone, os.address AS shipping_address,
            p.name
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        JOIN order_shipping os ON o.order_id = os.order_id
        JOIN products p ON oi.product_id = p.product_id
        WHERE oi.product_id = ?`,
        [productId]
    );
    return rows;
};

// 获取商家发布的商品所有购买订单
const getOrdersBySeller = async (sellerId) => {
    const query = `
        SELECT 
            o.order_id, o.order_status, o.total_price, o.created_at,
            oi.quantity, oi.price, oi.product_id, 
            os.recipient_name, os.recipient_phone, os.address AS shipping_address,
            p.name AS product_name,
            u.username AS seller_username   -- 加入商家用户名字段
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        JOIN order_shipping os ON o.order_id = os.order_id
        JOIN products p ON oi.product_id = p.product_id
        JOIN users u ON p.user_id = u.user_id  -- 关联 users 表以获取商家的用户名
        WHERE p.user_id = ?
        ORDER BY o.created_at DESC;  -- 只查找指定商家发布的商品订单
    `;

    // 执行查询
    const [rows] = await db.promise().query(query, [sellerId]);
    // 格式化时间字段并转换时区
    rows.forEach(order => {
        // 将时间转换到指定时区，并格式化
        order.created_at = dayjs(order.created_at).utc().tz('Asia/Shanghai').format('YYYY-MM-DD HH:mm:ss'); // 转换到上海时区
    });
    return rows;
};

// 编辑订单

const editOrder = async (orderId, recipientName, recipientPhone, address) => {
    // 更新 order_shipping 表中的配送信息
    const updateShippingQuery = `
        UPDATE order_shipping
        SET recipient_name = ?, recipient_phone = ?, address = ?
        WHERE order_id = ?;
    `;
    const [shippingResult] = await db.promise().query(updateShippingQuery, [recipientName, recipientPhone, address, orderId]);

    // 如果更新了配送表的地址，更新 orders 表中的 shipping_address 字段
    if (shippingResult.affectedRows > 0) {
        const updateOrderAddressQuery = `
            UPDATE orders
            SET shipping_address = ?
            WHERE order_id = ?;
        `;
        const [orderResult] = await db.promise().query(updateOrderAddressQuery, [address, orderId]);

        return { shippingResult, orderResult };
    } else {
        throw new Error('配送信息更新失败');
    }
};



// 删除订单
const deleteOrder = async (orderId) => {
    const query = `DELETE FROM orders WHERE order_id = ?`;
    const [result] = await db.promise().query(query, [orderId]);
    return result;
};

// 取消订单
const cancelOrder = async (orderId) => {
    const [result] = await db.promise().execute(
        `UPDATE orders SET order_status = 'cancelled' WHERE order_id = ? AND order_status = 'confirmed'`,
        [orderId]
    );
    return result;
};

// 在现有代码后添加新方法
const getUserOrderList = async (userId, status = 'ALL') => {
    let query = `
     SELECT o.order_id, o.order_status, o.total_price, o.created_at,
           oi.quantity, oi.price,
           p.name as product_name, p.image_url as product_image,
					 p.description,
           os.recipient_name, os.recipient_phone, os.address
    FROM orders o
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    LEFT JOIN products p ON oi.product_id = p.product_id
    LEFT JOIN order_shipping os ON o.order_id = os.order_id
    WHERE o.user_id = ?
  `;

    if (status !== 'ALL') {
        query += ` AND o.order_status = ?`;
    }

    query += ` ORDER BY o.created_at DESC`;

    const [rows] = await db.promise().query(
        query,
        status === 'ALL' ? [userId] : [userId, status]
    );

    return rows;
};
const getUserGroupbuyOrderList = async (userId) => {
    const [rows] = await db.promise().query(
        `SELECT 
    gb.groupbuy_id,
    gb.product_id,
    p.name AS product_name,
    p.description AS product_description,
    p.price AS product_price,
    p.stock AS product_stock,
    p.category AS product_category,
    p.image_url AS product_image_url,
    gb.group_size,
    gb.current_count,
    gb.status,
    gb.creator_user_id,
    u.username AS creator_username,
    gb.is_paid,
    gb.end_time
FROM 
    groupbuy_participants gbp
JOIN 
    groupbuy gb ON gbp.groupbuy_id = gb.groupbuy_id
JOIN 
    products p ON gb.product_id = p.product_id
JOIN 
    users u ON gb.creator_user_id = u.user_id 
WHERE 
    gbp.user_id = ?`,
        [userId]
    );
    return rows;
};

// 将新方法添加到导出对象中
module.exports = {
    ...module.exports,
    getUserOrderList,
    getUserGroupbuyOrderList,
    createOrder,
    createOrderItems,
    createOrderShipping,
    updateOrderStatus,
    getOrderDetails,
    getOrderById,
    getOrdersByProduct,
    getOrdersBySeller,
    editOrder,
    deleteOrder,
    cancelOrder
};
